﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;

namespace SqLiteEditor.Helper
{
    
    public static class SqliteHelper
    {
        // Fields
        private static string connectionString;
        private static SQLiteConnection mConnection;

        public static void ChechOpenDataBase()
        {
            if (mConnection == null) return;
            if (mConnection.State == ConnectionState.Closed)
                mConnection.Open();
        }

        public static void ChechCloseDataBase()
        {
            if (mConnection == null) return;
            if (mConnection.State == ConnectionState.Open)
                mConnection.Close();
        }
                
        public static void Connect(string fileName,bool New = false)
        {
            if ( ! New )
                connectionString = "Data Source=" + fileName + ";Version=3;Pooling=True;";
            else
                connectionString = "Data Source=" + fileName + ";Version=3;Pooling=True;New=True;";

            mConnection = new SQLiteConnection(connectionString);

            mConnection.Open();

        }

        public static void disConnect()
        {
            if (mConnection == null || mConnection.State == ConnectionState.Closed) return;

            connectionString = String.Empty;
            mConnection.Close();
        }

        private static string mLastExecuteTime;
        
        public static string LastExecuteTime
        {
            get { return mLastExecuteTime; }
            set { mLastExecuteTime = value; }
        }

        public static string SqliteVersion
        {
            get { return "SQLite : " + mConnection.ServerVersion; }
        }

        public static string GetDML(DataRow tabel)
        {
            return tabel.ItemArray[6].ToString();
        }

        public static DataRowCollection GetTabels()
        {
            ChechOpenDataBase();

            DataTable tables = mConnection.GetSchema("Tables");

            System.Data.DataRowCollection datarow = tables.Rows;

            return datarow;

        }

        public static List<string> GetTabelsName()
        {
            ChechOpenDataBase();

            List<string> list = new List<string>();

            DataTable tables = mConnection.GetSchema("Tables");

            System.Data.DataRowCollection datarow = tables.Rows;

            foreach (DataRow tabel in datarow)
            {
                list.Add(tabel.ItemArray[2].ToString());
            }

            return list;
        }

        public static DataRowCollection GetViews()
        {
            ChechOpenDataBase();

            DataTable views = mConnection.GetSchema("Views");

            System.Data.DataRowCollection datarow = views.Rows;

            return datarow;

        }

        public static DataRowCollection GetIndexes()
        {
            ChechOpenDataBase();

            DataTable indexes = mConnection.GetSchema("Indexes");
            System.Data.DataRowCollection datarow = indexes.Rows;
            return datarow;
        }

        public static DataRowCollection GetTriggers()
        {
            ChechOpenDataBase();

            DataTable triggers = mConnection.GetSchema("Triggers");
            System.Data.DataRowCollection datarow = triggers.Rows;
            return datarow;
        }

        public static List<TTabelStructure> GetColumnFieldType(string tabelname)
        {
            ChechOpenDataBase();

            string[] data = new string[10];

            string query = "Select * from " + tabelname;

            SQLiteCommand command = new SQLiteCommand(query, mConnection);
            SQLiteDataReader reader = command.ExecuteReader();


            List<TTabelStructure> fieldInfo = new List<TTabelStructure>();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                var isprimary = (reader.GetSchemaTable().Rows[i].ItemArray[5]);

                TTabelStructure fielsinfo = new TTabelStructure(reader.GetName(i), reader.GetDataTypeName(i), reader.GetFieldType(i).Name, (bool)isprimary, true);

                fieldInfo.Add(fielsinfo);
            }

            return fieldInfo;
        }

        //public static DataTable GetTableColumns(String tableName) 
        //{
        //    List<String> columns = new List<String>();
        //    String query = "pragma table_info(" + tableName + ");";

        //    ChechOpenDataBase();

        //    SQLiteCommand command = new SQLiteCommand(query, mConnection);

        //    SQLiteDataAdapter dataadapter = new SQLiteDataAdapter(command);
        //    DataTable dtb = new DataTable();
        //    dataadapter.Fill(dtb);

        //    ChechCloseDataBase();

        //    return dtb;
        // }


        public static List<string> GetColumnFieldsNames(string tabelname)
        {
            List<string> fieldName = new List<string>();

            List<TTabelStructure> columnFields = GetColumnFieldType(tabelname);

            foreach (TTabelStructure field in columnFields)
            {
                fieldName.Add(field.ColumnName);
            }

            return fieldName;
        }

        private static string AllowNull(bool value)
        {
            if (!value)
                return "NOT NULL";
            else
                return "";
        }

        private static string IsAutoIncrement(bool value)
        {
            if (!value)
                return "";
            else
                return ""; //"AUTOINCREMENT";
        }

        private static string IsCheckExist(bool value)
        {
            if (!value)
                return "";
            else
                return "IF NOT EXISTS ";
        }

        public static void CreateTable(List<TTabelStructure> structTableList, string TableName, bool CheckExist)
        {
            string Query = Query_CreateTable(structTableList, TableName, CheckExist);
            ExecuteNonQuery(Query);
        }

        public static string Query_CreateTable(List<TTabelStructure> structTableList, string TableName, bool CheckExist)
        {
            string query = "Create Table " + IsCheckExist(CheckExist) + TableName + "(" + Environment.NewLine;

            int Count = structTableList.Count;
            int index = 0;

            foreach (TTabelStructure item in structTableList)
            {
                string qut = ",";
                if (index == Count) qut = "";

                if (String.IsNullOrEmpty(item.ColumnName)) break;
                query += item.ColumnName + "  " + item.DataType + "  " + AllowNull(item.IsAllowNull) + qut + Environment.NewLine;
                index++;
            }

            //Set Primarykey
            foreach (TTabelStructure item in structTableList)
            {
                if (item.IsPrimaryKey)
                    query += "PRIMARY KEY " + IsAutoIncrement(item.IsAutoIncrement)  + " (" + item.ColumnName + ")" + Environment.NewLine;
            }

             query += Environment.NewLine;

            //Set UNIQUE
             foreach (TTabelStructure item in structTableList)
            {
                if (item.IsUNIQUE)
                    query += "UNIQUE (" + item.ColumnName + ")";
            }

            query += ");";

            return query;
        }

        public static string Query_RenameTable(string OldName, string NewName)
        {
            return "ALTER TABLE " + OldName + " RENAME TO " + NewName + ";";
        }

        public static string Query_CopyTable(string SrcTable, string DestTable)
        {
            string query = "INSERT INTO " + DestTable + " SELECT * FROM " + SrcTable;
            return query;
        }

        public static void  CopyTable(string SrcTable, string DestTable)
        {
            string query = Query_CopyTable(SrcTable,DestTable);
            ExecuteNonQuery(query);
        }

        public static void RenameTable(string OldName, string NewName)
        {
            string Query = Query_RenameTable(OldName, NewName);
            ExecuteNonQuery(Query);
        }

        public static string Query_RenameIndex(string OldName, string NewName)
        {
            return "ALTER INDEX " + OldName + " RENAME TO " + NewName + ";";
        }

        public static void RenameIndex(string OldName, string NewName)
        {
            ChechOpenDataBase();

            DataTable indexes = mConnection.GetSchema("Indexes");
            System.Data.DataRowCollection datarow = indexes.Rows;

            string Query = String.Empty;

            foreach (DataRow index in datarow)
            {
                if (index.ItemArray[5].Equals(OldName))
                {
                    Query = index.ItemArray[25].ToString();
                    break;
                }
            }

            if (String.IsNullOrEmpty(Query))
            {
                throw new InvalidExpressionException("Rename Index is Failed !");
                return;
            }

            //Replase New Index Name by New name
            string newQuery = Query.Replace(OldName, NewName);


            if (String.IsNullOrEmpty(newQuery))
            {
                throw new InvalidExpressionException("Rename Index is Failed !");
            }


            ChechOpenDataBase();

            SQLiteTransaction transaction;
            transaction = mConnection.BeginTransaction(System.Data.IsolationLevel.Serializable);

            int start = Environment.TickCount;

            //Create Query for Drop Old Index
            string QueryDropIndex = Query_DropIndex(OldName);

            try
            {
                //Drop Index by Transaction
                SQLiteCommand DropIndexcommand = new SQLiteCommand(QueryDropIndex, mConnection);
                DropIndexcommand.Transaction = transaction;
                DropIndexcommand.ExecuteNonQuery();

                SQLiteCommand CreateIndexcommand = new SQLiteCommand(newQuery, mConnection);
                CreateIndexcommand.Transaction = transaction;
                CreateIndexcommand.ExecuteNonQuery();

                transaction.Commit();
            }
            catch (SQLiteException ex)
            {
                transaction.Rollback();
                throw ex; 
            }

            int end = Environment.TickCount;

            TimeSpan time = TimeSpan.FromMilliseconds(end - start);

            LastExecuteTime = time.ToString();

            ChechCloseDataBase();
        }

        public static string Query_AddColumn(string _TableName, string _ColumnName, string _DataType)
        {
            return "ALTER TABLE " + _TableName + " ADD COLUMN " + _ColumnName + _DataType + ";";
        }

        public static void RenameColumn(string _TableName, string _OldColumnName,string _NewColumnName)
        {

            if (_OldColumnName.Equals(_NewColumnName)) return;
            
            if ( mConnection.State == ConnectionState.Closed )
                mConnection.Open();

            //Save Table Name
            string TableName = _TableName;
            string tempTableName = TableName + "_Temp";

            //Get Table Structure
            List<TTabelStructure> Fields = SqliteHelper.GetColumnFieldType(_TableName);
            
            //rename Column
            foreach( var f in Fields )
            {
                if (f.ColumnName == _OldColumnName)
                {
                    f.ColumnName = _NewColumnName;
                    break;
                }
            }

            //Create NewTable
            CreateTable(Fields, tempTableName, true);

            //CopyData In New Table
            CopyTable(TableName, tempTableName);

            DropTable(TableName);

            RenameTable(tempTableName, TableName);

            ChechCloseDataBase();

        }

        public static string Query_DropTable(string _TableName)
        {
            return "DROP TABLE " + _TableName + ";";
        }

        public static void DropTable(string _TableName)
        {
            string query_DropTable = Query_DropTable(_TableName);
            ExecuteNonQuery(query_DropTable);
        }

        public static string Query_DropView(string _ViewName)
        {
            return "DROP View " + _ViewName + ";";
        }

        public static string Query_DropIndex(string _IndexName)
        {
            return "DROP INDEX " + _IndexName + ";";
        }

        private static string IsUnique(bool value)
        {
            return value ? "UNIQUE" : "";
        }

        /// <summary>
        /// example 
        /// Field1 = Id
        /// Field1 = Name
        /// Field1 = Family
        /// return = Id,Name,Family
        /// </summary>
        /// <param name="fields"></param>
        /// <returns>string</returns>
        private static string GenerateFieldsString(List<string> fields)
        {
            string str = String.Empty;
            foreach (string item in fields)
                str += item + ",";

            str = str.Remove(str.Length - 1);
            return str;
        }

        public static string Query_AddIndex(string _IndexName, List<string> fields, bool _IsUnique, string tablename)
        {
            return String.Format("CREATE {0} INDEX {1} ON {2} ( {3} );", IsUnique(_IsUnique), _IndexName, tablename, GenerateFieldsString(fields));
        }

        public static string Query_AddView(string _ViewName, List<string> fields, string tablename)
        {
            return String.Format("CREATE VIEW {0} AS SELECT {1} FROM {2} ;", _ViewName, GenerateFieldsString(fields), tablename);
        }

        public static void Update(DataTable data)
        {
            ChechOpenDataBase();

            string selectCommand = data.ExtendedProperties["SelectCommand"].ToString();
            if (string.IsNullOrEmpty(selectCommand))
                throw new NullReferenceException("SelectCommand");
            SQLiteCommand command = new SQLiteCommand(selectCommand, mConnection);

            SQLiteDataAdapter dataadapter = new SQLiteDataAdapter(command);


            SQLiteCommandBuilder cm = new SQLiteCommandBuilder(dataadapter);
            dataadapter.UpdateCommand = cm.GetUpdateCommand(false);

            dataadapter.Update(data);

            ChechCloseDataBase();
        }

        public static DataTable ExecutQuery(string quey)
        {
            ChechOpenDataBase();

            int start = Environment.TickCount;

            SQLiteCommand command = new SQLiteCommand(quey, mConnection);

            SQLiteDataAdapter dataadapter = new SQLiteDataAdapter(command);
            DataTable dtb = new DataTable();
            dtb.Clear();
            dataadapter.Fill(dtb);
            dtb.ExtendedProperties.Add("SelectCommand", quey);
            mConnection.Close();

            int end = Environment.TickCount;

            TimeSpan time = TimeSpan.FromMilliseconds(end - start);

            LastExecuteTime = time.ToString();

            ChechCloseDataBase();

            return dtb;
        }

        public static void ExecuteNonQuery(string quey)
        {
            ChechOpenDataBase();

            int start = Environment.TickCount;

            SQLiteCommand command = new SQLiteCommand(quey, mConnection);
            command.ExecuteNonQuery();

            mConnection.Close();

            int end = Environment.TickCount;

            TimeSpan time = TimeSpan.FromMilliseconds(end - start);

            LastExecuteTime = time.ToString();

            ChechCloseDataBase();

        }

        public static DataTable SelectTop(string tableName)
        {
            ChechOpenDataBase();

            string query = "SELECT * FROM " + tableName + ";";
            SQLiteCommand command = new SQLiteCommand(query, mConnection);

            SQLiteDataAdapter dataadapter = new SQLiteDataAdapter(command);
            DataTable dtb = new DataTable();
            dataadapter.Fill(dtb);

            ChechCloseDataBase();

            return dtb;
        }


    }
}
